
alter proc sp_create_update_wafer_store_lot (

@part_number	varchar(50), 
@lot_number		varchar(50), 
@qty_in			int,
@qty_out		int,
@date_in		datetime,
@date_out		datetime,
@inventory_type varchar(50),
@ps_type_name	varchar(50),
@vendor_name	varchar(50),
@wafer_Qty_in	int,
@wafer_id		varchar(100),
@status			varchar(50),
@fab_Lot_Number varchar(50),
@lot_Category	varchar(50),
@date_code		varchar(50),
@po_number		varchar(50) )
as
begin

	declare @process_step_id int
	declare @location_id int
	declare @lot_id int
	declare @this_lot_status varchar(50)

	declare @date_time datetime
	declare @start_date datetime
	declare @end_date datetime
	DECLARE @LOT_DATEIN DATETIME

	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end 	
	
	if @fab_Lot_Number is null
	begin
		set @fab_Lot_Number = @LOT_NUMBER
	end 
	
	select @location_id = Location_ID from Location where L_Type = @inventory_type and L_Vendor_Name = @vendor_name
	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name like @inventory_type and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
	if @process_step_id is null -- new Product or Product Name mismatch
	begin
		select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number like @part_number and PS_Type_Name like @inventory_type and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID
		if @process_step_id is null
		begin
			DECLARE @RMA_Process_Step VARCHAR(50)	
			-- SINCE WE ARE GETTING RMA LOTS IN WAFER STORE WITH THE BUMPING PART NUMBER, WE ARE TRYING TO FETCH THE APPROPRIATE PROCESS STEP ID
			SET @RMA_Process_Step = 'Bumping'
		
			SELECT @process_step_id = PS.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND P.Part_Number = @part_number AND PS_Type_Name = @RMA_Process_Step
			IF @process_step_id IS NULL
			BEGIN
				SELECT TOP 1 @process_step_id = PS.ProcessStep_ID FROM Product_Map_Table PMT, Process_Step PS WHERE PMT.Part_Number = @part_number AND PS.PS_Type = @RMA_Process_Step AND PMT.ProcessStep_ID = PS.ProcessStep_ID
				IF @process_step_id IS NULL
				BEGIN
					IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @lot_number AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @inventory_type AND IS_USER_NOTIFIED = 1 ) = 0 
					BEGIN
						INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp)
							VALUES ( @part_number, @lot_number, @fab_Lot_Number, @vendor_name, @inventory_type,'Discrepancy Lot Notification', @wafer_qty_in, @qty_in, @wafer_Id, 'Open', CURRENT_TIMESTAMP)
					END
					update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
					return
				END							
			END
			SELECT TOP 1 @process_step_id = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @process_step_id ) WHERE PS_TYPE = 'Wafer Store'
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		END
	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
	END

	select @this_lot_status = Lot_Current_Status, @lot_id = Lot_ID, @LOT_DATEIN = DATE_IN from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	
	IF @DATE_IN IS NULL AND @LOT_DATEIN IS NULL
    BEGIn
        SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
    END
    ELSE
    BEGIN
         IF @DATE_IN IS NULL AND @LOT_DATEIN IS NOT NULL
		 BEGIN	
			SET @DATE_IN = @LOT_DATEIN
		 END
    END
	
	if @lot_id is null -- Check if the lot exists
	begin -- Create Lot
		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Date_In, Date_Out,Lot_Current_Status, Timestamp,Lot_Entered_By,Lot_Category, Lot_Drop_Out,LOT_DATE_CODE, PO_NUMBER) 
			values (@process_step_id, @location_id, @lot_number,@fab_Lot_Number,@lot_number,@wafer_Qty_in, @date_in,@date_out, @status, CURRENT_TIMESTAMP,1,@lot_Category, 0, @date_code, @po_number )
		select @lot_id = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp)
				VALUES ( @part_number, @lot_number, @fab_Lot_Number, @vendor_name, @INVENTORY_TYPE, 'New Lot Notification', @wafer_qty_in, @Qty_In, @wafer_Id, 'Open', CURRENT_TIMESTAMP)
		
		Insert into Inventory_Lot (Lot_ID, IL_Wafer_Qty_In, IL_Date_In, TimeStamp) values (@lot_id, @wafer_Qty_in, @date_in, CURRENT_TIMESTAMP )		
		
	END
	ELSE
	BEGIN		
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS, WAFER_QTY_IN = @WAFER_QTY_IN, LOT_CATEGORY = 'PROD', LOT_DROP_OUT = 0, LOT_DATE_CODE= @date_code, PO_NUMBER = @PO_NUMBER WHERE LOT_ID = @LOT_ID		

		UPDATE INVENTORY_LOT SET IL_WAFER_QTY_IN = @WAFER_QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID		
	END
	
	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @fab_Lot_Number, @WAFER_QTY_IN, @DATE_IN
END
